/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sadhar.sipp.perekamanunitposbiayakhusus;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.sadhar.errhandler.ClassAntiNull;
import org.sadhar.sia.common.ClassConnection;
import org.sadhar.sia.common.ClassUtility;
import org.springframework.jdbc.core.RowMapper;

/**
 * 
 * @author Deny Prasetyo
 */
public class PerekamanUnitPosBiayaKhususDAOImpl implements
		PerekamanUnitPosBiayaKhususDAO {
	/*
	 * select pbku.idPosBiayaKhususUnit,kb.kodeKelompokBiaya,kb.tahunKelompok,
	 * u.Kd_unit_kerja,u.Nama_unit_kerja,pb.namaPosBiaya from
	 * ppmk.posbiayakhususunit pbku inner join ppmk.kelompokBiaya kb on
	 * (pbku.kodekelompokBiaya = kb.kodekelompokBiaya) inner join kamus.unkerja
	 * u on (pbku.kodeUnitKerja = u.Kd_unit_kerja) inner join ppmk.posbiaya pb
	 * on (pb.kodePosBiaya = kb.kodePosBiaya) where kb.tahunKelompok = ? order
	 * by u.Kd_unit_kerja;
	 */

	@Override
	public List<Map> listDetails(String tahun) {
		String sql = "select pbku.idPosBiayaKhususUnit,kb.kodeKelompokBiaya,kb.tahunKelompok, "
				+ " u.Kd_unit_kerja,u.Nama_unit_kerja,pb.namaPosBiaya "
				+ " from ppmk.posBiayaKhususUnit pbku "
				+ " inner join ppmk.kelompokBiaya kb on (pbku.kodeKelompokBiaya = kb.kodeKelompokBiaya) "
				+ " inner join kamus.unkerja u on (pbku.kodeUnitKerja = u.Kd_unit_kerja) "
				+ " inner join ppmk.posBiaya pb on (pb.kodePosBiaya = kb.kodePosBiaya) "
				+ " where kb.tahunKelompok = ? " + " order by u.Kd_unit_kerja ";

		List<Map> result = ClassConnection.getJdbc().queryForList(sql,
				new Object[] { ClassUtility.quote(tahun) });

		String currentNamaUnitKerja = "";
		for (Map m : result) {

			if (currentNamaUnitKerja.equalsIgnoreCase(m.get("Nama_unit_kerja")
					.toString())) {
				m.put("Nama_unit_kerja", "");
				m.put("Nama_unit_kerjaHidden", currentNamaUnitKerja);
			} else {
				currentNamaUnitKerja = m.get("Nama_unit_kerja").toString();
				m.put("Nama_unit_kerjaHidden", "");
			}
		}
		return result;
	}

	@Override
	public List<Map> listDetailsByTahunAndUnitKerja(String tahun,
			String kodeUnitKerja) {
		String sql = "select pbku.idPosBiayaKhususUnit,kb.kodeKelompokBiaya,kb.tahunKelompok, "
				+ " u.Kd_unit_kerja,u.Nama_unit_kerja,pb.namaPosBiaya"
				+ " from ppmk.posBiayaKhususUnit pbku "
				+ " inner join ppmk.kelompokBiaya kb on (pbku.kodeKelompokBiaya = kb.kodeKelompokBiaya) "
				+ " inner join kamus.unkerja u on (pbku.kodeUnitKerja = u.Kd_unit_kerja) "
				+ " inner join ppmk.posBiaya pb on (pb.kodePosBiaya = kb.kodePosBiaya) "
				+ " where kb.tahunKelompok = ? and u.Kd_unit_kerja = ? ";

		List<Map> result = ClassConnection.getJdbc().queryForList(
				sql,
				new Object[] { ClassUtility.quote(tahun),
						ClassUtility.quote(kodeUnitKerja) });

		String currentNamaUnitKerja = "";
		for (Map m : result) {

			if (currentNamaUnitKerja.equalsIgnoreCase(m.get("Nama_unit_kerja")
					.toString())) {
				m.put("Nama_unit_kerja", "");
				m.put("Nama_unit_kerjaHidden", currentNamaUnitKerja);
			} else {
				currentNamaUnitKerja = m.get("Nama_unit_kerja").toString();
				m.put("Nama_unit_kerjaHidden", "");
			}
		}
		return result;
	}

	public PerekamanUnitPosBiayaKhususDAOImpl() {
		ClassConnection.getTransactionProxyFactoryBean().setTarget(this);
	}

	public List<Map> list() {
		return ClassConnection.getJdbc().queryForList(
				"SELECT * FROM ppmk.posBiayaKhususUnit");
	}

	public void insert(PerekamanUnitPosBiayaKhusus posBiaya) throws Exception {
		String sql = "INSERT INTO ppmk.posBiayaKhususUnit(kodeKelompokBiaya,kodeUnitKerja,namaTabelItem) VALUES(?,?,?)";
		ClassConnection.getJdbc().update(
				sql,
				new Object[] {
						Integer.valueOf(posBiaya.getKodeKelompokBiaya()),
						ClassUtility.quote(posBiaya.getKodeUnitKerja()),
						ClassUtility.quote(posBiaya.getNamaTableItem()) });
	}

	public void delete(PerekamanUnitPosBiayaKhusus posBiaya) throws Exception {
		String sql = "DELETE FROM ppmk.posBiayaKhususUnit WHERE idPosBiayaKhususUnit=?";
		ClassConnection.getJdbc().update(
				sql,
				new Object[] { Integer.valueOf(posBiaya
						.getIdPosBiayaKhususUnit()) });
	}

	public void update(PerekamanUnitPosBiayaKhusus posBiaya) throws Exception {
		String sql = "UPDATE ppmk.posBiayaKhususUnit SET kodeKelompokBiaya=?,kodeUnitKerja=?,namaTabelItem=? WHERE idPosBiayaKhususUnit=? ";
		ClassConnection.getJdbc().update(
				sql,
				new Object[] {
						Integer.valueOf(posBiaya.getKodeKelompokBiaya()),
						ClassUtility.quote(posBiaya.getKodeUnitKerja()),
						ClassUtility.quote(posBiaya.getNamaTableItem()),
						Integer.valueOf(posBiaya.getIdPosBiayaKhususUnit()) });
	}

	public PerekamanUnitPosBiayaKhusus select(int idPostBiayaKhususUnit)
			throws Exception {
		String sql = "SELECT * FROM ppmk.posBiayaKhususUnit WHERE idPosBiayaKhususUnit=?";
		return (PerekamanUnitPosBiayaKhusus) ClassConnection.getJdbc()
				.queryForObject(sql, new Object[] { idPostBiayaKhususUnit },
						new RowMapper() {

							public Object mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								return new PerekamanUnitPosBiayaKhusus(rs
										.getInt("idPosBiayaKhususUnit"), rs
										.getInt("kodeKelompokBiaya"), rs
										.getString("kodeUnitKerja"), rs
										.getString("namaTableItem"));
							}
						});
	}

	@Override
	public void deleteAll(String kodeUnitKeja) throws Exception {
		String sql = "DELETE FROM ppmk.posBiayaKhususUnit WHERE kodeUnitKerja=?";
		ClassConnection.getJdbc().update(sql,
				new Object[] { ClassUtility.quote(kodeUnitKeja) });
	}

	@Override
	public long countByKodeKelompokBiaya(int kodeKelompokBiaya)throws Exception {
		String sql = "SELECT count(*) as jml FROM ppmk.posBiayaKhususUnit p where p.kodeKelompokBiaya = ?";
		List<Map> result = ClassConnection.getJdbc().queryForList(sql,new Object[]{Integer.valueOf(kodeKelompokBiaya)});
		
		long jml = 0;
		for(Map m : result){
			jml = ClassAntiNull.AntiNullLong(m.get("jml"));
		}
		return jml;
	}

	@Override
	public void deleteByKodeKelompokBiaya(int kodeKelompokBiaya)
			throws Exception {
		String sql = "DELETE FROM ppmk.posBiayaKhususUnit WHERE kodeKelompokBiaya=?";
		ClassConnection.getJdbc().update(sql,
				new Object[] { Integer.valueOf(kodeKelompokBiaya) });
		
	}
}
